﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using System.Data;

namespace acessaDB
{
    public class conectaDBMySql
    {
        public void abrirConexao(MySqlConnection conexao)
        {
            try
            {
                if (conexao.State == System.Data.ConnectionState.Closed)
                    conexao.Open();
            }
            catch
            { }
        }

        public void fecharConexao(MySqlConnection conexao)
        {
            try
            {
                if (conexao.State == System.Data.ConnectionState.Open)
                    conexao.Close();
            }
            catch
            { }
        }

        public DataSet retornaDataSet(string strQuery, string connectionString)
        {
            DataSet dsRetorno = new DataSet();
            MySqlConnection mySqlConexao = new MySqlConnection(connectionString);
            MySqlCommand mySqlComando = new MySqlCommand();
            MySqlDataAdapter mySqlAdapter = new MySqlDataAdapter();
            try
            {
                abrirConexao(mySqlConexao);
                mySqlComando.CommandType = CommandType.Text;
                mySqlComando.CommandText = strQuery;
                mySqlComando.Connection = mySqlConexao;
                mySqlComando.ExecuteNonQuery();

                mySqlAdapter.SelectCommand = mySqlComando;
                mySqlAdapter.Fill(dsRetorno);

                return dsRetorno;
            }
            finally
            {
                fecharConexao(mySqlConexao);
                dsRetorno.Dispose();
                mySqlConexao.Dispose();
                mySqlComando.Dispose();
                mySqlAdapter.Dispose();
            }
        }

        public MySqlDataReader retornaDataReader(string strQuery, string connectionString, MySqlConnection mySqlConexao)
        {
            MySqlCommand mySqlComando = new MySqlCommand();
            try
            {
                abrirConexao(mySqlConexao);
                mySqlComando.CommandType = CommandType.Text;
                mySqlComando.CommandText = strQuery;
                mySqlComando.Connection = mySqlConexao;
                MySqlDataReader drRetorno = mySqlComando.ExecuteReader();
                return drRetorno;
            }
            finally
            {
                //fecharConexao(mySqlConexao);     ***** PARA OBJETOS DATAREADER, A CONEXAO DEVE SER FECHADA, NO METODO QUE O CHAMA *****
                //mySqlConexao.Dispose();                  ********** SOMENTE APOS A VARRADURA DO MESMO *********** 
                //mySqlComando.Dispose();
                //mySqlAdapter.Dispose();
            }
        }

        //Classe para execução de comando
        public string _ExecutarComandoRetornandoID(string strQuery, string connectionString, bool semRetornoID = true, string usu_login = "SYSTEM")
        {
            MySqlConnection mySqlConexao = new MySqlConnection(connectionString);
            string idObjeto = "0";
            try
            {
                abrirConexao(mySqlConexao);
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = strQuery.ToString();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = mySqlConexao;
                cmd.ExecuteNonQuery();


                MySqlCommand ident = new MySqlCommand("SELECT @@IDENTITY AS idTabela", mySqlConexao);
                MySqlDataReader ident_result = ident.ExecuteReader();
                try { idObjeto = ident_result.GetDecimal(0).ToString(); }
                catch { idObjeto = ""; }

                //metodo de gravação de auditoria
                if (semRetornoID == true)
                {
                    if (ident_result.Read())
                    {
                        try
                        {
                            return ident_result.GetDecimal(0).ToString();
                        }
                        catch (Exception)
                        {
                            return "0";
                        }
                    }
                }

                return "0";
            }

            finally
            {
                fecharConexao(mySqlConexao);
                auditoria_dml(strQuery, idObjeto, usu_login, connectionString);
            }
        }

        public string _ExecutarStoredProcedure(string procedureName,
                                               string[] parameters,
                                               string connectionString,
                                               string usu_login = "SYSTEM",
                                               string parametroRetorno = "0")
        {
            MySqlConnection mySqlConexao = new MySqlConnection(connectionString);
            try
            {
                abrirConexao(mySqlConexao);
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = mySqlConexao;
                MySqlParameter parametros = new MySqlParameter();
                if (parametroRetorno != "0")
                {
                    parametros = cmd.Parameters.Add(parametroRetorno, MySqlDbType.Int32);
                    parametros.Direction = ParameterDirection.ReturnValue;
                }
                foreach (string myParam in parameters)
                {
                    parametros = cmd.Parameters.Add(myParam, MySqlDbType.Int32, 12);
                }

                //cmd.CommandText = strQuery.ToString();
                cmd.ExecuteNonQuery();

                return "0";
            }

            finally
            {
                fecharConexao(mySqlConexao);
            }
        }

        public void auditoria_dml(string strQuery, string idObjeto, string usuario, string connectionString)
        {
            MySqlConnection mySqlConexao = new MySqlConnection();
            try
            {
                abrirConexao(mySqlConexao);
                if (!string.IsNullOrEmpty(usuario))
                {
                    strQuery = strQuery.ToUpper().Trim();
                    string action = "";
                    string tabela = "";
                    string values = "''";
                    if ((string.IsNullOrEmpty(idObjeto)) || (idObjeto == "0")) idObjeto = "NULL";
                    string audStatement = "''";

                    if (strQuery.IndexOf("INSERT") > -1)
                    {
                        strQuery = strQuery.Replace("INSERT", "").Trim();
                        strQuery = strQuery.Replace("INTO", "").Trim();
                        tabela = strQuery.Substring(0, strQuery.IndexOf(" ", 0));
                        strQuery = strQuery.Substring(strQuery.IndexOf("VALUES", 0), strQuery.Length - strQuery.IndexOf("VALUES", 0));
                        values = "'" + strQuery.Replace("VALUES", "") + "'";
                        action = "'INSERT'";
                    }
                    else if (strQuery.IndexOf("UPDATE") > -1)
                    {
                        strQuery = strQuery.Replace("UPDATE ", "");
                        tabela = strQuery.Substring(0, strQuery.IndexOf(" ", 0));
                        //
                        values = strQuery.Substring(strQuery.IndexOf("SET", 0), strQuery.Length - strQuery.IndexOf("SET", 0)).Replace("SET", "");
                        values = "'" + strQuery.Remove(strQuery.IndexOf("WHERE", 0)) + "'";
                        values = values.Replace(tabela, "");
                        values = values.Replace("SET", "");
                        //
                        strQuery = strQuery.Substring(strQuery.IndexOf("WHERE", 0), strQuery.Length - strQuery.IndexOf("WHERE", 0));
                        audStatement = "'" + strQuery + "'";
                        action = "'UPDATE'";
                    }
                    else if (strQuery.IndexOf("DELETE") > -1)
                    {
                        strQuery = strQuery.Replace("DELETE FROM ", "");
                        tabela = strQuery.Substring(0, strQuery.IndexOf(" ", 0));
                        strQuery = strQuery.Substring(strQuery.IndexOf("WHERE", 0), strQuery.Length - strQuery.IndexOf("WHERE", 0));
                        audStatement = "'" + strQuery + "'";
                        action = "'DELETE'";
                    }

                    if ((!string.IsNullOrEmpty(tabela)) && (!string.IsNullOrEmpty(action)))
                    {
                        try
                        {
                            MySqlCommand cmdAudit = new MySqlCommand();
                            cmdAudit.CommandText = "EXECUTE [dbo].[SP_AUDITORIA_DML] '" +
                                                    usuario + "', '" +
                                                    tabela + "', " +
                                                    idObjeto + ", " +
                                                    action + ", " +
                                                    audStatement + ", " +
                                                    values;
                            cmdAudit.CommandType = CommandType.Text;
                            cmdAudit.Connection = mySqlConexao;
                            cmdAudit.ExecuteNonQuery();
                        }
                        catch { }
                    }
                }
            }
            finally
            {
                fecharConexao(mySqlConexao);
            }
        }

    }
}